package com.lxyk.dao.user;

import com.lxyk.domain.Classes;
import org.apache.ibatis.annotations.*;

import java.util.Date;
import java.util.List;

public interface ClassesDao {

    //查询所有班级
    @Select("select * from classes order by openTime desc")
    public List<Classes> findAll();

    //查询所有在读班级
    @Select("select * from classes where statusClass=0")
    public List<Classes> findAllStart();

    //查询所有毕业班级
    @Select("select * from classes where statusClass=1")
    public List<Classes> findAllEnd();

    //根据id查询
    @Select("select * from classes where id=#{id}")
    public Classes findById(Integer id);

    //添加
    @Insert("insert into classes values(null,#{className},#{openTime},#{closeTime},#{statusClass},#{speed}," +
            "#{classSize},#{effeNumber})")
    @SelectKey(keyColumn = "id", keyProperty = "id", resultType = Integer.class, before = false, statement = {"select last_insert_id()"})
    public Integer save(Classes classes);

    //删除
    @Delete("delete from classes where id=#{id}")
    public Integer delete(Integer id);

    //更新
    @Update("update classes set className=#{className},openTime=#{openTime},closeTime=#{closeTime}," +
            "statusClass=#{statusClass},speed=#{speed},classSize=#{classSize},effeNumber=#{effeNumber} " +
            "where id=#{id}")
    public Integer update(Classes classes);

    //根据用户id查询对应班级
    @Select("SELECT c.* FROM `classes` c,`user_class` uc where c.id=uc.cid and uc.uid=#{uid}")
    public List<Classes> findByUser(Integer uid);

    //修改班级总人数
    @Update("update classes set classSize=classSize+#{num} where id=#{cid}")
    public void editTotalNum(@Param("cid") Integer cid,@Param("num")Integer num);

    //修改班级有效人数
    @Update("update classes set effeNumber=effeNumber+#{num} where id=#{cid}")
    public void editEffeNum(@Param("cid") Integer cid,@Param("num")Integer num);

    //结课
    @Update("update classes set statusClass=1 where id=#{cid}")
    public void endClasses(Integer cid);

    //---------------用户班级关系表----------------------

    //根据id查询出班级对应的用户
    @Select("select uid from user_class where cid=#{id}")
    public List<Integer> findTeacherByCid(Integer id);

    //根据班级id清空班级用户中间表
    @Delete("delete from user_class where cid=#{cid}")
    void removeByCid(Integer cid);

    //插入新的关系数据
    @Insert("insert into user_class values (#{uid},#{cid})")
    void add(@Param("uid") Integer uid, @Param("cid") Integer cid);

    //---------------查询学生表----------------------
    //根据班级统计退费人数
    @Select("SELECT count(id) FROM `student` where cid = #{cid} and refundFlag=1")
    Integer findOutNum(Integer cid);

    //根据班级统计本周退费人数
    @Select("SELECT\n" +
            "\tcount(*)\n" +
            "FROM\n" +
            "\t`student` s,\n" +
            "\trefund r\n" +
            "where\n" +
            "\ts.id=r.sid\n" +
            "AND s.cid = #{cid}\n" +
            "AND s.refundFlag = 1\n" +
            "AND r.applyTime BETWEEN DATE_FORMAT(\n" +
            "\tDATE_SUB(\n" +
            "\t\tNOW(),\n" +
            "\t\tINTERVAL WEEKDAY(NOW()) DAY\n" +
            "\t),\n" +
            "\t'%Y-%m-%d 00:00:00'\n" +
            ")\n" +
            "AND DATE_FORMAT(\n" +
            "\tSUBDATE(\n" +
            "\t\tCURDATE(),\n" +
            "\t\tDATE_FORMAT(CURDATE(), '%w') - 7\n" +
            "\t),\n" +
            "\t'%Y-%m-%d 23:59:59'\n" +
            ")")
    Integer findOutNumWeek(Integer cid);

    //查询班级昨日进班人数
    @Select("SELECT count(id) FROM `student` where cid = #{cid} and enrollTime>=#{start} and enrollTime<=#{end}")
    Integer findJoinNum(@Param("cid")Integer cid, @Param("start") Date start, @Param("end")Date end);

    //根据班级id查询本周进班人数
    @Select("SELECT\n" +
            "\tcount(id)\n" +
            "FROM\n" +
            "\t`student`\n" +
            "WHERE\n" +
            "\tcid = #{cid}\n" +
            "AND enrollTime BETWEEN DATE_FORMAT(\n" +
            "\tDATE_SUB(\n" +
            "\t\tNOW(),\n" +
            "\t\tINTERVAL WEEKDAY(NOW()) DAY\n" +
            "\t),\n" +
            "\t'%Y-%m-%d 00:00:00'\n" +
            ")\n" +
            "AND DATE_FORMAT(\n" +
            "\tSUBDATE(\n" +
            "\t\tCURDATE(),\n" +
            "\t\tDATE_FORMAT(CURDATE(), '%w') - 7\n" +
            "\t),\n" +
            "\t'%Y-%m-%d 23:59:59'\n" +
            ")")
    Integer findJoinNumWeek(Integer cid);

}
